#!/usr/bin/env node

/**
 * 数据库表结构导出脚本
 * 从远程MySQL数据库导出所有表的结构定义
 * 
 * 使用方法:
 * 1. 设置环境变量或使用默认值
 * 2. 运行: node export-database-schema.js
 * 
 * 环境变量:
 * - DB_HOST: 数据库主机地址
 * - DB_PORT: 数据库端口
 * - DB_USER: 数据库用户名
 * - DB_PASSWORD: 数据库密码
 * - DB_NAME: 数据库名称
 */

const mysql = require('mysql2/promise');
const fs = require('fs').promises;
const path = require('path');

// 数据库连接配置
const dbConfig = {
    host: process.env.DB_HOST || '43.143.253.188',
    port: parseInt(process.env.DB_PORT) || 3306,
    user: process.env.DB_USER || 'nextjs',
    password: process.env.DB_PASSWORD || 'yEAiRE2jcxTyRBy5',
    database: process.env.DB_NAME || 'nextjs',
    charset: 'utf8mb4'
};

// 输出目录
const OUTPUT_DIR = path.join(__dirname, 'schemas');

class DatabaseSchemaExporter {
    constructor() {
        this.connection = null;
        this.tables = [];
        this.mysqlVersion = null;
    }

    /**
     * 连接数据库
     */
    async connect() {
        try {
            console.log('🔌 正在连接数据库...');
            console.log(`主机: ${dbConfig.host}:${dbConfig.port}`);
            console.log(`数据库: ${dbConfig.database}`);
            console.log(`用户: ${dbConfig.user}`);

            this.connection = await mysql.createConnection(dbConfig);
            console.log('✅ 数据库连接成功');

            // 获取MySQL版本信息
            await this.getMySQLVersion();
        } catch (error) {
            console.error('❌ 数据库连接失败:', error.message);
            throw error;
        }
    }

    /**
     * 获取MySQL版本信息
     */
    async getMySQLVersion() {
        try {
            const [rows] = await this.connection.execute('SELECT VERSION() as version');
            this.mysqlVersion = rows[0].version;
            console.log(`🔍 MySQL版本: ${this.mysqlVersion}`);
        } catch (error) {
            console.log('⚠️ 无法获取MySQL版本信息');
            this.mysqlVersion = 'unknown';
        }
    }

    /**
     * 获取所有表名
     */
    async getTables() {
        try {
            console.log('📋 正在获取表列表...');

            const [rows] = await this.connection.execute(`
                SELECT TABLE_NAME 
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = ? 
                AND TABLE_TYPE = 'BASE TABLE'
                ORDER BY TABLE_NAME
            `, [dbConfig.database]);

            this.tables = rows.map(row => row.TABLE_NAME);
            console.log(`✅ 找到 ${this.tables.length} 个表`);

            return this.tables;
        } catch (error) {
            console.error('❌ 获取表列表失败:', error.message);
            throw error;
        }
    }

    /**
     * 获取表结构
     */
    async getTableStructure(tableName) {
        try {
            // 获取CREATE TABLE语句
            const [createRows] = await this.connection.execute(`SHOW CREATE TABLE \`${tableName}\``);
            const createStatement = createRows[0]['Create Table'];

            // 获取表信息
            const [tableInfo] = await this.connection.execute(`
                SELECT 
                    TABLE_COMMENT,
                    TABLE_COLLATION,
                    ENGINE,
                    TABLE_ROWS,
                    AVG_ROW_LENGTH,
                    DATA_LENGTH,
                    MAX_DATA_LENGTH,
                    INDEX_LENGTH,
                    AUTO_INCREMENT,
                    CREATE_TIME,
                    UPDATE_TIME
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
            `, [dbConfig.database, tableName]);

            // 获取列信息
            const [columns] = await this.connection.execute(`
                SELECT 
                    COLUMN_NAME,
                    COLUMN_TYPE,
                    IS_NULLABLE,
                    COLUMN_KEY,
                    COLUMN_DEFAULT,
                    EXTRA,
                    COLUMN_COMMENT,
                    CHARACTER_SET_NAME,
                    COLLATION_NAME,
                    ORDINAL_POSITION
                FROM information_schema.COLUMNS 
                WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
                ORDER BY ORDINAL_POSITION
            `, [dbConfig.database, tableName]);

            // 获取索引信息
            const [indexes] = await this.connection.execute(`
                SELECT 
                    INDEX_NAME,
                    COLUMN_NAME,
                    NON_UNIQUE,
                    SEQ_IN_INDEX,
                    COLLATION,
                    CARDINALITY,
                    SUB_PART,
                    PACKED,
                    NULLABLE,
                    INDEX_TYPE,
                    COMMENT
                FROM information_schema.STATISTICS 
                WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
                ORDER BY INDEX_NAME, SEQ_IN_INDEX
            `, [dbConfig.database, tableName]);

            // 获取外键信息 - 兼容不同版本的MySQL
            let foreignKeys = [];
            try {
                // 首先尝试获取MySQL 8.0+的完整外键信息
                const [fkResult] = await this.connection.execute(`
                    SELECT 
                        CONSTRAINT_NAME,
                        COLUMN_NAME,
                        REFERENCED_TABLE_NAME,
                        REFERENCED_COLUMN_NAME,
                        UPDATE_RULE,
                        DELETE_RULE
                    FROM information_schema.KEY_COLUMN_USAGE 
                    WHERE TABLE_SCHEMA = ? 
                    AND TABLE_NAME = ? 
                    AND REFERENCED_TABLE_NAME IS NOT NULL
                `, [dbConfig.database, tableName]);

                foreignKeys = fkResult;
            } catch (fkError) {
                // 如果失败，尝试获取基本外键信息（兼容MySQL 5.7及以下版本）
                try {
                    const [fkResult] = await this.connection.execute(`
                        SELECT 
                            CONSTRAINT_NAME,
                            COLUMN_NAME,
                            REFERENCED_TABLE_NAME,
                            REFERENCED_COLUMN_NAME,
                            NULL as UPDATE_RULE,
                            NULL as DELETE_RULE
                        FROM information_schema.KEY_COLUMN_USAGE 
                        WHERE TABLE_SCHEMA = ? 
                        AND TABLE_NAME = ? 
                        AND REFERENCED_TABLE_NAME IS NOT NULL
                    `, [dbConfig.database, tableName]);

                    foreignKeys = fkResult;
                } catch (basicFkError) {
                    console.log(`⚠️ 获取表 ${tableName} 外键信息失败，跳过外键信息`);
                    foreignKeys = [];
                }
            }

            return {
                tableName,
                createStatement,
                tableInfo: tableInfo[0] || {},
                columns,
                indexes,
                foreignKeys
            };
        } catch (error) {
            console.error(`❌ 获取表 ${tableName} 结构失败:`, error.message);
            return null;
        }
    }

    /**
     * 生成表结构文档
     */
    generateTableDocument(structure) {
        if (!structure) return '';

        const { tableName, createStatement, tableInfo, columns, indexes, foreignKeys } = structure;

        let doc = `-- 表名: ${tableName}\n`;
        doc += `-- 描述: ${tableInfo.TABLE_COMMENT || '无描述'}\n`;
        doc += `-- 引擎: ${tableInfo.ENGINE || '未知'}\n`;
        doc += `-- 字符集: ${tableInfo.TABLE_COLLATION || '未知'}\n`;
        doc += `-- 行数: ${tableInfo.TABLE_ROWS || '未知'}\n`;
        doc += `-- 创建时间: ${tableInfo.CREATE_TIME || '未知'}\n`;
        doc += `-- 更新时间: ${tableInfo.UPDATE_TIME || '未知'}\n`;
        doc += `-- 数据长度: ${tableInfo.DATA_LENGTH || '未知'} 字节\n`;
        doc += `-- 索引长度: ${tableInfo.INDEX_LENGTH || '未知'} 字节\n\n`;

        // CREATE TABLE语句
        doc += `-- 建表语句\n`;
        doc += `${createStatement};\n\n`;

        // 列信息
        if (columns.length > 0) {
            doc += `-- 列信息\n`;
            doc += `-- 序号 | 列名 | 类型 | 可空 | 键 | 默认值 | 额外 | 注释\n`;
            doc += `-- -----|------|------|------|----|---------|------|------\n`;

            columns.forEach(col => {
                const nullable = col.IS_NULLABLE === 'YES' ? '是' : '否';
                const key = col.COLUMN_KEY || '';
                const extra = col.EXTRA || '';
                const comment = col.COLUMN_COMMENT || '';

                doc += `-- ${col.ORDINAL_POSITION.toString().padStart(2)} | ${col.COLUMN_NAME.padEnd(20)} | ${col.COLUMN_TYPE.padEnd(20)} | ${nullable.padEnd(2)} | ${key.padEnd(2)} | ${(col.COLUMN_DEFAULT || '').padEnd(8)} | ${extra.padEnd(8)} | ${comment}\n`;
            });
            doc += '\n';
        }

        // 索引信息
        if (indexes.length > 0) {
            doc += `-- 索引信息\n`;
            doc += `-- 索引名 | 列名 | 唯一性 | 类型 | 注释\n`;
            doc += `-- -------|------|--------|------|------\n`;

            const uniqueIndexes = {};
            indexes.forEach(idx => {
                if (!uniqueIndexes[idx.INDEX_NAME]) {
                    uniqueIndexes[idx.INDEX_NAME] = {
                        name: idx.INDEX_NAME,
                        columns: [],
                        unique: idx.NON_UNIQUE === 0,
                        type: idx.INDEX_TYPE,
                        comment: idx.COMMENT || ''
                    };
                }
                uniqueIndexes[idx.INDEX_NAME].columns.push(idx.COLUMN_NAME);
            });

            Object.values(uniqueIndexes).forEach(idx => {
                const unique = idx.unique ? '唯一' : '非唯一';
                doc += `-- ${idx.name.padEnd(12)} | ${idx.columns.join(',').padEnd(20)} | ${unique.padEnd(6)} | ${idx.type.padEnd(8)} | ${idx.comment}\n`;
            });
            doc += '\n';
        }

        // 外键信息
        if (foreignKeys.length > 0) {
            doc += `-- 外键信息\n`;
            doc += `-- 约束名 | 列名 | 引用表 | 引用列 | 更新规则 | 删除规则\n`;
            doc += `-- --------|------|--------|--------|----------|----------\n`;

            foreignKeys.forEach(fk => {
                const updateRule = fk.UPDATE_RULE || '未知';
                const deleteRule = fk.DELETE_RULE || '未知';
                doc += `-- ${fk.CONSTRAINT_NAME.padEnd(12)} | ${fk.COLUMN_NAME.padEnd(8)} | ${fk.REFERENCED_TABLE_NAME.padEnd(8)} | ${fk.REFERENCED_COLUMN_NAME.padEnd(8)} | ${updateRule.padEnd(8)} | ${deleteRule}\n`;
            });
            doc += '\n';
        }

        doc += `-- 表结构导出完成\n`;
        doc += `-- 导出时间: ${new Date().toISOString()}\n\n`;

        return doc;
    }

    /**
     * 导出单个表结构
     */
    async exportTable(tableName) {
        try {
            console.log(`📝 正在导出表: ${tableName}`);

            const structure = await this.getTableStructure(tableName);
            if (!structure) {
                console.log(`⚠️ 跳过表 ${tableName} (获取结构失败)`);
                return false;
            }

            const document = this.generateTableDocument(structure);
            const fileName = `${tableName}.sql`;
            const filePath = path.join(OUTPUT_DIR, fileName);

            await fs.writeFile(filePath, document, 'utf8');
            console.log(`✅ 表 ${tableName} 导出成功: ${fileName}`);

            return true;
        } catch (error) {
            console.error(`❌ 导出表 ${tableName} 失败:`, error.message);
            return false;
        }
    }

    /**
     * 导出所有表结构
     */
    async exportAllTables() {
        try {
            // 创建输出目录
            await fs.mkdir(OUTPUT_DIR, { recursive: true });
            console.log(`📁 输出目录: ${OUTPUT_DIR}`);

            // 获取表列表
            const tables = await this.getTables();
            if (tables.length === 0) {
                console.log('⚠️ 没有找到任何表');
                return;
            }

            // 导出每个表
            let successCount = 0;
            let failCount = 0;

            for (const tableName of tables) {
                const success = await this.exportTable(tableName);
                if (success) {
                    successCount++;
                } else {
                    failCount++;
                }
            }

            // 生成汇总报告
            await this.generateSummaryReport(tables, successCount, failCount);

            console.log('\n🎉 导出完成!');
            console.log(`✅ 成功: ${successCount} 个表`);
            console.log(`❌ 失败: ${failCount} 个表`);
            console.log(`📁 输出目录: ${OUTPUT_DIR}`);

        } catch (error) {
            console.error('❌ 导出过程失败:', error.message);
        }
    }

    /**
     * 生成汇总报告
     */
    async generateSummaryReport(tables, successCount, failCount) {
        try {
            const report = `# 数据库表结构导出报告

## 基本信息
- 数据库: ${dbConfig.database}
- 主机: ${dbConfig.host}:${dbConfig.port}
- MySQL版本: ${this.mysqlVersion || '未知'}
- 导出时间: ${new Date().toISOString()}
- 总表数: ${tables.length}
- 成功导出: ${successCount}
- 失败导出: ${failCount}

## 表列表
${tables.map((table, index) => `${index + 1}. ${table}`).join('\n')}

## 导出文件
每个表的结构都保存在对应的 \`.sql\` 文件中，包含：
- 建表语句
- 列信息
- 索引信息
- 外键信息（如果可用）
- 表统计信息

## 注意事项
- 所有文件使用 UTF-8 编码
- 时间格式使用 ISO 8601 标准
- 文件按表名命名，便于查找
- 外键信息可能因MySQL版本而异
`;

            const reportPath = path.join(OUTPUT_DIR, 'README.md');
            await fs.writeFile(reportPath, report, 'utf8');
            console.log(`📋 汇总报告已生成: README.md`);

        } catch (error) {
            console.error('❌ 生成汇总报告失败:', error.message);
        }
    }

    /**
     * 关闭数据库连接
     */
    async close() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

/**
 * 主函数
 */
async function main() {
    const exporter = new DatabaseSchemaExporter();

    try {
        await exporter.connect();
        await exporter.exportAllTables();
    } catch (error) {
        console.error('❌ 程序执行失败:', error.message);
        process.exit(1);
    } finally {
        await exporter.close();
    }
}

// 如果直接运行此脚本
if (require.main === module) {
    main();
}

module.exports = DatabaseSchemaExporter;
